<?php
	class Baocao_model extends CI_Model
	{
		function doanhthungay($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
			$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, Day(NgayDatPhong) as day, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1 AND Month(NgayDatPhong) <= $m2)
						GROUP BY NgayDatPhong
						ORDER BY  NgayDatPhong ASC ";
			}
			else
			{
				$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, Day(NgayDatPhong) as day, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2)
						GROUP BY (NgayDatPhong)
						ORDER BY  NgayDatPhong ASC ";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;
		}

		function doanhthuthang($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
			$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1 AND Month(NgayDatPhong) <= $m2)
						GROUP BY Year(NgayDatPhong), Month(NgayDatPhong)
						ORDER BY  Year(NgayDatPhong) ASC, Month(NgayDatPhong) ASC ";
			}
			else
			{
				$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2)
						GROUP BY Year(NgayDatPhong), Month(NgayDatPhong)
						ORDER BY  Year(NgayDatPhong) ASC, Month(NgayDatPhong) ASC ";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;	
		}
		function doanhthunam($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
			$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE Year(NgayDatPhong)=$y1
						GROUP BY Year(NgayDatPhong)
						ORDER BY  Year(NgayDatPhong) ASC ";
			}
			else
			{
				$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)>= $y1 AND Year(NgayDatPhong) <= $y2)
						GROUP BY Year(NgayDatPhong)
						ORDER BY  Year(NgayDatPhong) ASC ";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;		
		}
		
		function LayDSKS()
		{
			$strSQL = "SELECT MaKS as maks,Ten ten FROM KhachSan ORDER BY  ten asc";
			
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;	
		}
		function tatcakhachsanngay($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, Day(p.NgayDatPhong) as day, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1 AND Month(p.NgayDatPhong) >= $m1 AND Month(p.NgayDatPhong) <= $m2)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY p.NgayDatPhong, ks.MaKS
						ORDER BY  p.NgayDatPhong ASC ";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, Day(p.NgayDatPhong) as day, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY p.NgayDatPhong, ks.MaKS
						ORDER BY  ks.MaKS DESC, p.NgayDatPhong ASC";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;		
		}
		
		function khachsanngay($date1,$date2, $loaiks) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, Day(p.NgayDatPhong) as day, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1 AND Month(p.NgayDatPhong) >= $m1 AND Month(p.NgayDatPhong) <= $m2)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY p.NgayDatPhong, ks.MaKS
						ORDER BY  p.NgayDatPhong ASC ";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, Day(p.NgayDatPhong) as day, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY p.NgayDatPhong, ks.MaKS
						ORDER BY  ks.MaKS DESC, p.NgayDatPhong ASC";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;		
		}
		
		function tatcakhachsanthang($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1 AND Month(p.NgayDatPhong) >= $m1 AND Month(p.NgayDatPhong) <= $m2)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY Year(p.NgayDatPhong), Month(p.NgayDatPhong), ks.MaKS
						ORDER BY Year(p.NgayDatPhong) ASC, Month(p.NgayDatPhong) ASC ";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY Year(p.NgayDatPhong), Month(p.NgayDatPhong), ks.MaKS
						ORDER BY  Year(p.NgayDatPhong) ASC, Month(p.NgayDatPhong) ASC ";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;		
		}
		
		function khachsanthang($date1,$date2, $loaiks) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1 AND Month(p.NgayDatPhong) >= $m1 AND Month(p.NgayDatPhong) <= $m2)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY Year(p.NgayDatPhong), Month(p.NgayDatPhong), ks.MaKS
						ORDER BY Year(p.NgayDatPhong) ASC, Month(p.NgayDatPhong) ASC ";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY Year(p.NgayDatPhong), Month(p.NgayDatPhong), ks.MaKS
						ORDER BY  Year(p.NgayDatPhong) ASC, Month(p.NgayDatPhong) ASC ";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;
		}
		function tatcakhachsannam($date1,$date2) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY Year(p.NgayDatPhong), ks.MaKS
						ORDER BY Year(p.NgayDatPhong) ASC";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)>= $y1 AND Year(NgayDatPhong) <= $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS
						GROUP BY Year(p.NgayDatPhong), ks.MaKS
						ORDER BY  Year(p.NgayDatPhong) ASC";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;
		}
		function khachsannam($date1,$date2,$loaiks) // đang hợp tác OK
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE (Year(p.NgayDatPhong)=$y1)
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY Year(p.NgayDatPhong), ks.MaKS
						ORDER BY Year(p.NgayDatPhong) ASC";
			
			}
			else
			{
				$strSQL = "SELECT Year(p.NgayDatPhong) as year, Month(p.NgayDatPhong) as month, sum(p.TongTien) as doanhthu, ks.Ten as tenks, ks.MaKS as maks
						FROM PhieuDatPhong as p, KhachSan as ks, LoaiPhong as lp
						WHERE ((Year(NgayDatPhong)>= $y1 AND Year(NgayDatPhong) <= $y2))
						and p.TinhTrangThanhToan = 1 and p.LoaiPhong = lp.MaLP and lp.KhachSan = ks.MaKS and ks.MaKS = $loaiks
						GROUP BY Year(p.NgayDatPhong), ks.MaKS
						ORDER BY  Year(p.NgayDatPhong) ASC";
			}
			$query = $this->db->query($strSQL);		
			if($query->num_rows() > 0)
			{
				foreach($query->result() as $row)
				{
					$data[] = $row;
				}
				return $data;
			}
			return NULL;
		}
		
		function xuatfile()
		{
			
			 $query = $this->db->get('NhanVien');
 
        if(!$query)
            return false;
 
        // Starting the PHPExcel library
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
 
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
 
        $objPHPExcel->setActiveSheetIndex(0);
 
        // Field names in the first row
        $fields = $query->list_fields();
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }
 
        // Fetching the table data
        $row = 2;
        foreach($query->result() as $data)
        {
            $col = 0;
            foreach ($fields as $field)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
                $col++;
            }
 
            $row++;
        }
 
        $objPHPExcel->setActiveSheetIndex(0);
 
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
 
        // Sending headers to force the user to download the file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
        header('Cache-Control: max-age=0');
 
        $objWriter->save('output');
    }
	
		function downloaddoanhthungay($date1, $date2)
		{
			$m1 = intval(substr($date1,0,2));
			$y1 = intval(substr($date1,3));
			$m2 =  intval(substr($date2,0,2));
			$y2 =  intval(substr($date2,3));
			if($y1 == $y2)
			{
			$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, Day(NgayDatPhong) as day, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1 AND Month(NgayDatPhong) <= $m2)
						GROUP BY NgayDatPhong
						ORDER BY  NgayDatPhong ASC ";
			}
			else
			{
				$strSQL = "SELECT Year(NgayDatPhong) as year, Month(NgayDatPhong) as month, Day(NgayDatPhong) as day, sum(TongTien) as doanhthu
						FROM PhieuDatPhong
						WHERE (Year(NgayDatPhong)=$y1 AND Month(NgayDatPhong) >= $m1) OR (Year(NgayDatPhong)=$y2 AND Month(NgayDatPhong) <= $m2)
						OR (Year(NgayDatPhong)>$y1 AND Year(NgayDatPhong) < $y2)
						GROUP BY (NgayDatPhong)
						ORDER BY  NgayDatPhong ASC ";
			}
			$query = $this->db->query($strSQL);	
			if($query->num_rows() > 0)
			{	
				$data = array(array());
				$i =0;
				$temp1 = 0;
				$temp2 = 0;
				foreach($query->result() as $row)
				{
					$nam = $row->year;
					$thang = $row->month;
					$ngay = $row->day;
					$doanhthu = $row->doanhthu;
					if($temp1 != $nam)
					{
						$temp1 = $nam;
						$temp2 = $thang;
						$data[$i]["Nam"] = $nam;
						$data[$i]["Thang"] = $thang;
						$data[$i]["Ngay"] = $ngay;
						$data[$i]["Doanh thu"] = $doanhthu;
					}
					else
					{
						$data[$i]["Nam"] = "";
						if($temp2 != $thang)
						{
							$temp2 = $thang;
							$data[$i]["Thang"] = $thang;
						}
						else
						{
							$data[$i]["Thang"] = "";
						}
						$data[$i]["Ngay"] = $ngay;
						$data[$i]["Doanh thu"] = $doanhthu;		
					}
					$i++;
				}
				$this->export($data);
			}
			return NULL;
		}
		
		function export($data)
		{
			$filename = "maitram_data_" . date('Ymd') . ".xls";

			header("Content-Disposition: attachment; filename=\"$filename\"");
			header("Content-Type: application/vnd.ms-excel");
		
			/*$flag = false;
			foreach($data as $row)
			{
				if(!$flag) 
				{
			  	// display field/column names as first row
			  		$temp = implode("\t", array_keys($row)) . "\r\n";
			  		$flag = true;
				}
				//array_walk($row,"cleanData");
				//array_walk($row,array($this,"cleanData"));

				$temp .= implode("\t", array_values($row)) . "\r\n";
  			}
			$this->load->helper('download');
			force_download($file_name, $temp);*/
		}
		
		function cleanData(&$str)
		{
			/*$str = preg_replace("/\t/", "\\t", $str);
			$str = preg_replace("/\r?\n/", "\\n", $str);*/
			if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
		}
	
		function downloaddoanhthuthang($date1, $date2)
		{
			
		}
		
		function downloaddoanhthunam($date1, $date2)
		{
			
		}

		
	}
?>